Καλησπέρα και Καλή Χρονιά σε όλους.

έχω ένα πίνακα σε μια βάση του MS SQL Server (2008) όπου θεωρητικά θα αποτελείται από αρκετές χιλιάδες εγγραφές ίσως και εκατομμύρια.
Θέλω λοιπόν να δημιουργήσω έναν δεύτερο πίνακα όπου θα περιέχει στοιχεία από υπολογισμούς μεταξύ των εγγραφών του προηγούμενου πίνακα και εξηγώ:

έστω ο Πίνακας Α με πεδία {id, AttributeA, AttributeB, AttributeC κ.λπ.}
και με τιμές:
{1,α,β,γ}
{2,δ,ε,ζ}
{3,η,θ,ι}
{4,κ,λ,μ}
θέλω λοιπόν βάση ενός αλγορίθμου να γίνονται κάποιοι υπολογισμοί και να προκύπτει πίνακας Β με πεδία {id1, id2, NewCol}
και με τιμές:
{1,2, (α-δ)^2+(β-ε)^2+(γ-ζ)^2}
{1,3, (α-η)^2+(β-θ)^2+(γ-ι)^2}
{1,4, (α-κ)^2+(β-λ)^2+(γ-μ)^2}
{2,3, (δ-η)^2+(ε-θ)^2+(ζ-ι)^2}
{2,4, (δ-κ)^2+(ε-λ)^2+(ζ-μ)^2}
{3,4, (η-κ)^2+(θ-λ)^2+(ι-μ)^2}

στο μυαλό μου φέρνω έναν τρόπο να γίνει με φόρτωμα του πίνακα σε arraylist και με programming-loops να γίνουν οι υπολογισμοί που θα αποθηκεύονται σε ένα νέο arraylist και στη συνέχεια
θα το αποθηκεύω σε sql table
όμως ανησυχώ :
-για τα resources που θα καταναλώνονται από άποψη μνήμης (μεγάλος όγκος δεδομένων)
-για την ταχύτητα
-για την ορθότητα του εγχειρήματος από άποψη ποιότητας κώδικα

κάποιος φίλος με παρέπεμψε να δω και τα cursors στον sql server, με τα οποία όμως δεν έχω ξανα-ασχοληθεί και δεν τα κατέχω (και μετά από googling) δεν έχω κάποια σαφή εικόνα στο μυαλό μου...


Θα μπορούσε κάποιος να με διαφωτίσει σχετικά με το παραπάνω θέμα;

1. Υπάρχει περίπτωση τα cursors να είναι λύση στο πρόβλημά μου;
2. Ποια είναι η καλύτερη λύση σε ένα τέτοιο ζητούμενο;

Ευχαριστώ,

Καλή και Δημιουργική Χρονιά σε όλους

Μήπως εννοείς Triggers; Triggers που θα αναλάβουν να ενημερώνουν τον δεύτερο πίνακα κάθε φορά που γίνεται μια προσθήκη/μεταβολή/διαγραφή στον πρώτο. Όπως και νά 'χει, το πρόβλημα που αντιμετωπίζεις είναι πολύ πιο σύνθετο και πιστεύω ότι η ύπαρξη του δεύτερου πίνακα στη βάση είναι περιττή και θα δημιουργήσει πολύ περισσότερα προβλήματα. Η ώρα είναι περασμένη, αλλά από αυτά που γράφεις, προκύπτει ότι αν οι εγγραφές του πρώτου πίνακα είναι ν, οι εγγραφές του δεύτερου πίνακα θα είναι ν!/(2!*(ν-2)!) = ν*(ν-1)/2. Πρόκειται για το πλήθος των συνδυασμών των εγγραφών ανά δύο. Δηλαδή, αν ο πρώτος πίνακας έχει 100 εγγραφές, ο δεύτερος θα έχει 4.950. Αν, τώρα, ο πρώτος έχει 100.000 εγγραφές, ο δεύτερος θα έχει 4.999.950.000 (!). Δε νομίζω ότι υπάρχει database engine που να μπορεί να διαχειριστεί αγόγγυστα τέτοιον όγκο. Κατά τη γνώμη μου, το πιο λογικό είναι να φορτώνεις τις εγγραφές που θέλεις να επεξεργαστείς στη μνήμη (προς Θεού, ΟΧΙ όλες) και στη συνέχεια να δημιουργείς τον πίνακα στη μνήμη. Αν πάλι δεν έχω καταλάβει σωστά κάτι, είπαμε: Η ώρα είναι περασμένη!!

---

Ακόμα κι ένας άνθρωπος μπορεί ν' αλλάξει τον κόσμο. Μη θέλεις να κυβερνήσεις. Απλά δείξε το μονοπάτι κι ο κόσμος θ' ακολουθήσει!!

Καλημέρα και Καλή Χρονιά και σε εσένα με υγεία και ότι καλύτερο για την νέα χρονια.

Αν ο όγκος των δεδομένων σου σαν πρωτογενεί δεδομένα είναι μεγάλος και ο αλγόριθμος σου είναι αυτός που μας έχει δώσει σίγουρα θα έχεις θέμα σε resources και ταχύτητα.

Δοκίμασα αυτό που θέλεις σε ένα μικρό δείγμα δεδομενων (5000 rows) και έκανα εισαγωγή στον δεύτερο πίνακα που θέλεις σε 2' και 49'' 12.497.500 rows.

Σαν πρώτη λύση που έχω σκεφτεί σου προτείνω το παρακάτω, αν και πιστεύω ότι αν το βασανίσουμε λίγο περισσότερο θα βρεθεί καλύτερη λύση.

create database dnz
go

use dnz
go

create table t1 (id int identity(1,1), a1 int ,a2 int, a3 int)
go

create table t2 (id1 int, id2 int ,newval bigint)
go

-- insert 5000 rows for test
insert into t1(a1,a2,a3) values (1,2,3),(4,5,6),(7,8,9),(10,11,12),(13,14,15)
go 1000

select count(*) from t1
go

insert into t2
select y.id id1,x.id id2, POWER((y.a1-x.a1),2) + POWER((y.a2-x.a2),2) + POWER((y.a3-x.a3),2) newval
from t1 x cross join t1 y
where  x.id > y.id
order by 1,2

select * from t2

Μόλις βρω λίγο χρόνο ακόμα θα το βασανίσω λίγο ακόμα, αλλά μέχρι τότε καλό θα είναι να το δοκίμασεις και εσύ με μεγαλύτερο όγκο δεδομένων στο σύστημα σου

Φιλικά

---

Antonios Chatzipavlis

Μια ερώτηση (αν και φαντάζομαι οτι για να θέτεις το ζήτημα η απάντηση είναι ναι): Θέλεις να τα αποθηκεύεις πλήρως αυτά τα παραγόμενα δεδομένα, πραγματικά; Η απλά θέλεις να τρέχεις calculations / aggregates πάνω στα υπολογιζομενα πάνω σε ένα subset των δεδομένων αυτών κάθε φορά; Γιατί στην δεύτερη περίπτωση ίσως το πρόβλημά σου λυνόταν π.χ. με κάποιο table variable ή κάποια UDF (μιλώντας πάντοτε για μικρό subset).

---

Σωτήρης Φιλιππίδης

DotSee Web Services

@markos
Τα αρχικά δεδομένα μου εισάγονται μία φορά με BULK INSERT και δεν θα προσθέτονται άλλες εγγραφές (τουλάχιστον όσο το scope είναι το ίδιο). Επομένως τα trigggers δεν είναι η λύση που εξυπηρετεί το σκοπό αυτό...


Γενικότερα πρόκειται για έναν αλγόριθμο που υπολογίζει τις αποστάσεις μεταξύ των παρατηρήσεων χρησιμοποιώντας π.χ. την Ευκλίδεια απόσταση

@cap
Σωτήρη θέλω να αποθηκεύω τα αποτελέσματα γιατί στη συνέχεια θα πρέπει να δω ποια από αυτά ικανοποιούν κάποιες "δυναμικές" συνθήκες αφού ο χρήστης θα δίνει τιμές σε κάποιες μεταβλητές του και γενικά θα "παίζει" με αυτές...


@Antonios Chatzipavlis
Σε ευχαριστώ πολύ για το χρόνο σου, τρέχω να το δοκιμάσω και επανέρχομαι...


Γενικά απ'ότι είδα τα cursors δεν είναι πολύ προτιμητέα, απεναντίας μάλιστα (βέβαια δεν βρήκα και κάποιο παράδειγμα που να ταιριάζει στο θέμα μου)

Θα κάνω μια δοκιμή και θα τεστάρω τη λύση του stored procedure που δίνει ο Αντώνης και θα δοκιμάσω να τα φορτώσω και  στη μνήμη... να δούμε τι θα βγει...

Το να πας με cursor θα ήταν κατά την γνώμη μου αυτοκτονία καθώς θέλεις nested cursors.  Αυτό που σου έδωσα εγώ είναι ολόκληρο το παράδειγμα που έκανα. Στην ουσία όλα γίνονται με το παρακάτω query

insert into t2
select y.id id1,x.id id2, POWER((y.a1-x.a1),2) + POWER((y.a2-x.a2),2) + POWER((y.a3-x.a3),2) newval
from t1 x cross join t1 y
where  x.id > y.id
order by 1,2

Μάλιστα καλό θα ήταν να βγάλεις το order by καθώς βαραίνει την διαδικασία.

Πάντως ένα είναι σίγουρο θα αν θέλεις να τρέξει αυτό με την μια σε όλα τα rows που θα έχεις, ειδικά σε μεγάλο αριθμό, θέλεις μνήμη και μετά cpu

---

Antonios Chatzipavlis

Ευθύμης Δημόπουλος:

@cap
Σωτήρη θέλω να αποθηκεύω τα αποτελέσματα γιατί στη συνέχεια θα πρέπει να δω ποια από αυτά ικανοποιούν κάποιες "δυναμικές" συνθήκες αφού ο χρήστης θα δίνει τιμές σε κάποιες μεταβλητές του και γενικά θα "παίζει" με αυτές...

Για να μπορέσει κάποιος να σου δώσει μια πιο ορθή προσέγγιση πρέπει να περιγράψεις ποιες είναι αυτές οι συνθήκες, μήπως και η αποθήκευση όλων αυτών των "ενδιάμεσων" αποτελεσμάτων είναι περιττή. Αν μιλάς για εκατοντάδες χιλιάδες πρωτογενή δεδομένα, ο όγκος των αποτελεσμάτων των ενδιάμεσων υπολογισμών είναι τεράστιος. Το πιο κοντινό παράδειγμα που μπορώ να σκεφτώ στην περίπτωσή σου είναι οι συνδυασμοί του ΛΟΤΤΟ. Αν δεν κάνω λάθος, ο δειγματικός χώρος είναι περίπου 14.000.000 στήλες. Φυσικά, όλες αυτές έχουν κάποια χαρακτηριστικά όπως συνολικό άθροισμα, μέσο όρο, πλήθος μονών, ζυγών κ.λπ. Δεν ξέρω, όμως, κάποιο πρόγραμμα ΛΟΤΤΟ που να έχει προϋπολογίσει τα χαρακτηριστικά όλων αυτών των στηλών και να τα έχει αποθηκεύσει μόνιμα σε κάποια βάση δεδομένων, με σκοπό να "ξεσκαρτάρει" όσες δεν ικανοποιούν κάποια κριτήρια με Select statements. Το σενάριο αυτό κατά τη γνώμη μου δεν παίζει. Αν θέλεις, δώσε λίγο περισσότερα στοιχεία.

---

Ακόμα κι ένας άνθρωπος μπορεί ν' αλλάξει τον κόσμο. Μη θέλεις να κυβερνήσεις. Απλά δείξε το μονοπάτι κι ο κόσμος θ' ακολουθήσει!!

Markos:

Για να μπορέσει κάποιος να σου δώσει μια πιο ορθή προσέγγιση πρέπει να περιγράψεις ποιες είναι αυτές οι συνθήκες, μήπως και η αποθήκευση όλων αυτών των "ενδιάμεσων" αποτελεσμάτων είναι περιττή. 

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Ευθύμης Δημόπουλος:

...

Γενικότερα πρόκειται για έναν αλγόριθμο που υπολογίζει τις αποστάσεις μεταξύ των παρατηρήσεων χρησιμοποιώντας π.χ. την Ευκλίδεια απόσταση

@cap
Σωτήρη θέλω να αποθηκεύω τα αποτελέσματα γιατί στη συνέχεια θα πρέπει να δω ποια από αυτά ικανοποιούν κάποιες "δυναμικές" συνθήκες αφού ο χρήστης θα δίνει τιμές σε κάποιες μεταβλητές του και γενικά θα "παίζει" με αυτές...

...

To να υπολογίζεις και να φιλτράρεις on-the-fly θα είναι πολύ πιο γρήγορο από το να φιλτράρεις τα data της βάσης. 

Δηλαδή, input -> calculate -> filter -> drop ή store (ανάλογα τις συνθήκες)

Στην περίπτωση του να έχεις τα data στη βάση, θα πρέπει κάθε φορά να τα κάνεις fetch με το αντίστοιχο κόστος σε I/O αφού το αποθηκευτικό μέσο είναι πολύ πιο αργό από την μνήμη του PC. Βάσει των όσων έχεις περιγράψει, δεν βρίσκω το λόγο να κρατάς τα προ-υπολογισμένα αποτελέσματα στη βάση. Ο μόνος λόγος που μπορώ να σκεφτώ είναι να θες να κρατήσεις ιστορικότητα. Από την άλλη, τα αποτελέσματα (αφού φιλτράρεις τα data) τι θες να τα κάνεις;

Μήπως να μας εξηγήσεις το bigger picture του προβλήματος;

 

---

Vir prudens non contra ventum mingit

Markos:

Για να μπορέσει κάποιος να σου δώσει μια πιο ορθή προσέγγιση πρέπει να περιγράψεις ποιες είναι αυτές οι συνθήκες, μήπως και η αποθήκευση όλων αυτών των "ενδιάμεσων" αποτελεσμάτων είναι περιττή. Αν μιλάς για εκατοντάδες χιλιάδες πρωτογενή δεδομένα, ο όγκος των αποτελεσμάτων των ενδιάμεσων υπολογισμών είναι τεράστιος. Το πιο κοντινό παράδειγμα που μπορώ να σκεφτώ στην περίπτωσή σου είναι οι συνδυασμοί του ΛΟΤΤΟ. Αν δεν κάνω λάθος, ο δειγματικός χώρος είναι περίπου 14.000.000 στήλες. Φυσικά, όλες αυτές έχουν κάποια χαρακτηριστικά όπως συνολικό άθροισμα, μέσο όρο, πλήθος μονών, ζυγών κ.λπ. Δεν ξέρω, όμως, κάποιο πρόγραμμα ΛΟΤΤΟ που να έχει προϋπολογίσει τα χαρακτηριστικά όλων αυτών των στηλών και να τα έχει αποθηκεύσει μόνιμα σε κάποια βάση δεδομένων, με σκοπό να "ξεσκαρτάρει" όσες δεν ικανοποιούν κάποια κριτήρια με Select statements. Το σενάριο αυτό κατά τη γνώμη μου δεν παίζει. Αν θέλεις, δώσε λίγο περισσότερα στοιχεία.

Λοιπόν... όπως είπα θέλω να υπολογίζω τις αποστάσεις (με ένα συγκεκριμένο αλγόριθμο)
έστω ότι δημιουργείται ο Πίνακας Β' με πεδία (id1, id2, CalcAttrA, CalcAttrB, CalcAttrC)

create table t2 (id1 int, id2 int ,CalcAttrA int, CalcAttrB int, CalcAttrC int)
go
insert into t2
select y.id id1,x.id id2, ABS(y.a1-x.a1) CalcAttrA, ABS(y.a2-x.a2) CalcAttrB, ABS(y.a3-x.a3) CalcAttrC
from t1 x cross join t1 y
where  x.id > y.id

(με συγχωρείτε για την αλλαγή στη δομή, αλλά συνέβει λόγω ταχύτητας και της προχωρημένης χτεσινης ώρας)
(δεν επηρεάζει πολύ τη συνολική ιδέα)
Ο χρήστης θα ορίζει 3 thresholds T1, T2, T3
στη συνέχεια θα του εμφανίζονται τα ζευγάρια εκείνα για τα οποία θα ισχύει (σε απλοποιημένη μορφή)

CalcAttrA+CalcAttrB+CalcAttrC < Τ1
και αν το count Χ των CalcAttrA, CalcAttrB,CalcAttrC που CalcAttrA>Τ2 κ.λπ. είναι μικρότερος του T3

Αν δεν ικανοποιούν τα αποτελέσματα τον χρήστη τότε εκείνος αλλάζει τις τιμές στα thresholds T1,T2,T3 και επαναλαμβάνει...

(δεν ήθελα να σας μπλέξω... αλλά... )

Αν κατάλαβα καλά δηλαδή, ο χρήστης ορίζοντας τα tresholds αναζητά τιμές σε ολόκληρο το χώρο των calculated τιμών προκειμένου να ικανοποιηθούν τα κριτήρια και δεν μπορεί να περιοριστεί αυτός ο χώρος με κάποιο φίλτρο πάνω στους συνδυασμούς των πρωτογενών τιμών, σωστά;

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Κοίταξε να δεις... Έφτιαξα μια βάση δεδομένων με έναν πίνακα (Points). Γέμισα τον πίνακα με 10000 τριάδες τυχαίων αριθμών από το 0 μέχρι το 1000. Το παρακάτω query έφερε 822 rows σε περίπου 1 min.

Select DP1.id,DP1.x,DP1.y,DP1.z,DP2.id,DP2.x,DP2.y,DP2.z From Points As DP1
inner join (Select id,x,y,z From Points) DP2
on DP1.id>DP2.id
And (power(DP1.x-DP2.x,2) + power(DP1.y-DP2.y,2) + power(DP1.z-DP2.z,2)<500)
And (power(DP1.x-DP2.x,2)>100)

Η επιλογή είναι δική σου...

---

Ακόμα κι ένας άνθρωπος μπορεί ν' αλλάξει τον κόσμο. Μη θέλεις να κυβερνήσεις. Απλά δείξε το μονοπάτι κι ο κόσμος θ' ακολουθήσει!!

Μάρκο μου

Και το δικό σου  και το δικό μου query έχουν ακριβώς το ίδιο execution plan. Mάλιστα έχω και εγω φτιάξει μια βάση με 10.000 εγγραφές στον Τ1 πίνακα και δοκίμασα και τα δύο χωρις όμως το τελευταίο and το αποτέλεσμα είναι ακριβώς το ίδιο 10.995.00 εγγραφές και τα δύο και οι χρόνοι σχεδόν όμοιοι 1' 31'' το δικό μου 1' 33'' το δικό σου (σημ πρώτα έτρεξα το δικό μου).

Το θέμα είναι ότι ο φίλος μας αναγκαστικά έτσι όπως μας τα λεει θα φάει στο κεφάλι καρτεσιανό γινόμενο (φιλτραρισμένο μεν αλλά δεν παύει να είναι καρτεσιανό το οποίο προοδευτικά μειώνετε)

Και για του λόγου το αληθες

ΔΙΚΟ ΜΟΥ

select y.id id1,x.id id2, abs(y.a1-x.a1) , abs(y.a2-x.a2) , abs(y.a3-x.a3)  from t1 x cross join t1 y   where  x.id > y.id  and abs(y.a1-x.a1)+abs(y.a2-x.a2)+abs(y.a3-x.a3) < 500
  |--Parallelism(Gather Streams)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([ y].[id], [ y].[a1], [ y].[a2], [ y].[a3], [Expr1004]) WITH UNORDERED PREFETCH)
            |--Clustered Index Scan(OBJECT:([dnz].[dbo].[t1].[PK_t1] AS [ y]))
            |--Clustered Index Seek(OBJECT:([dnz].[dbo].[t1].[PK_t1] AS [x]), SEEK:([x].[id] > [dnz].[dbo].[t1].[id] as [ y].[id]),  WHERE:(((abs([dnz].[dbo].[t1].[a1] as [ y].[a1]-[dnz].[dbo].[t1].[a1] as [x].[a1])+abs([dnz].[dbo].[t1].[a2] as [ y].[a2]-[dnz].[dbo].[t1].[a2] as [x].[a2]))+abs([dnz].[dbo].[t1].[a3] as [ y].[a3]-[dnz].[dbo].[t1].[a3] as [x].[a3]))<(500)) ORDERED FORWARD)

ΔΙΚΟ ΣΟΥ

 select y.id id1,x.id id2--, abs(y.a1-x.a1) , abs(y.a2-x.a2) , abs(y.a3-x.a3)  from t1 x inner join ( select * from t1 ) y   on  x.id > y.id  and abs(y.a1-x.a1)+abs(y.a2-x.a2)+abs(y.a3-x.a3) < 500
  |--Parallelism(Gather Streams)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([dnz].[dbo].[t1].[id], [dnz].[dbo].[t1].[a1], [dnz].[dbo].[t1].[a2], [dnz].[dbo].[t1].[a3], [Expr1005]) WITH UNORDERED PREFETCH)
            |--Clustered Index Scan(OBJECT:([dnz].[dbo].[t1].[PK_t1]))
            |--Clustered Index Seek(OBJECT:([dnz].[dbo].[t1].[PK_t1] AS [x]), SEEK:([x].[id] > [dnz].[dbo].[t1].[id]),  WHERE:(((abs([dnz].[dbo].[t1].[a1]-[dnz].[dbo].[t1].[a1] as [x].[a1])+abs([dnz].[dbo].[t1].[a2]-[dnz].[dbo].[t1].[a2] as [x].[a2]))+abs([dnz].[dbo].[t1].[a3]-[dnz].[dbo].[t1].[a3] as [x].[a3]))<(500)) ORDERED FORWARD)

---

Antonios Chatzipavlis

OK, έχεις δίκιο. Απλά ήθελα να το δοκιμάσω κι εγώ. Δεν είμαι μαθηματικός, αλλά δεν βλέπω κάποιον τρόπο να περιοριστεί το αρχικό set των δεδομένων. Οπότε πάμε αναγκαστικά σε εκτίμηση όλων των δυνατών συνδυασμών. Επίσης, δεν υπάρχει λόγος να "χτυπάμε" τη βάση ξανά και ξανά, κάθε φορά που αλλάζουν τα όρια και πρέπει να τρέξουν από την αρχή τα queries. Φορτώνουμε τα δεδομένα στη μνήμη μια φορά και οι υπολογισμοί γίνονται εκεί.

---

Ακόμα κι ένας άνθρωπος μπορεί ν' αλλάξει τον κόσμο. Μη θέλεις να κυβερνήσεις. Απλά δείξε το μονοπάτι κι ο κόσμος θ' ακολουθήσει!!

Οχι δε το λέω σαν παρατήρηση προς εσένα, προς Θεού. Απλά το είδα το δικό σου και αμέσως σκέφτηκα ότι και αυτό cross join θα κάνει το δοκίμασα και είπα να σου πω το αποτέλεσμα.

Γεγονός πάντος είναι ότι δοκίμασα να βάλω στο άλλον πίνακα όλους τους δυνατούς συνδιασμούς των 10.000 εγγραφών που έχω στον αρχικό. Αυτό πήρε 9' 45'' και μπήκαν 49.995.000 εγγραφές.

Προσπάθησα να κάνω select all στο ταπεινό μου virtual machine αλλά μάταια έτρωγα πόρτα από την μνήμη. Με τα φίλτρα όμως του φίλου μας τα πράγματα ήταν αρκετά κάλα αλλά όχι τραγικά διαφορετικά από πριν.

Θα κάνω μερικές δοκιμές ακόμα που έχω στο μυαλό μου το βράδυ στο σπίτι καθώς τώρα δεν μπορώ. 

---

Antonios Chatzipavlis